﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient

Public Class DocumentoClienteBalanzaDatos

    Function registrarDocumentoAlmacenBalanza(dcb As DocumentoClienteBalanza) As String
        Dim mensaje As String = "Asignación correcta."

        If verificarExistencia(dcb) = True Then
            mensaje = "Asignación ya existe."
        Else
            Dim conex As New Conexion
            Dim coneccion As SqlConnection = conex.conectar()
            coneccion.Open()

            Dim query As String = "INSERT INTO Documento_Cliente_Balanza( " &
                                  "CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo, " &
                                  "BAL_Codigo,DCB_FechaInicio,DCB_FechaFin,DCB_Activo) " &
                                  "VALUES(@cia,@suc,@tdoc,@dciCodigo,@balanza,GETDATE(),NULL,@activo)"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@cia", dcb.CIA)
                .Parameters.AddWithValue("@suc", dcb.SUC_Codigo)
                .Parameters.AddWithValue("@tdoc", dcb.TDOC_Codigo)
                .Parameters.AddWithValue("@dciCodigo", dcb.DCI_Codigo)
                .Parameters.AddWithValue("@balanza", dcb.BAL_Codigo)
                .Parameters.AddWithValue("@activo", dcb.DCB_Activo)
                .ExecuteNonQuery()
            End With

            coneccion.Close()
        End If

        Return mensaje
    End Function

    Function obtenerCantidadPedidosBandejasXBalanza() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT b.BAL_Codigo, COUNT(DISTINCT(dcb.DCI_Codigo)) AS CantPed, COUNT(DISTINCT(dadpub.BAN_Codigo)) CantBand " &
                              "FROM Balanza b " &
                              "LEFT JOIN Documento_Cliente_Balanza dcb " &
                              "ON dcb.BAL_Codigo = b.BAL_Codigo " &
                              "LEFT JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.DCI_Codigo = dcb.DCI_Codigo AND dcad.DCAD_Activo = '1' " &
                              "LEFT JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "LEFT JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                              "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                              "LEFT JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " &
                              "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                              "LEFT JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                              "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' " &
                              "GROUP BY b.BAL_Codigo"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function verificarExistencia(dcb As DocumentoClienteBalanza) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT COUNT(*) AS CONTAR " &
                              "FROM Documento_Cliente_Balanza " &
                              "WHERE CIA = '" & dcb.CIA & "' AND SUC_Codigo = '" & dcb.SUC_Codigo & "' " &
                              "AND TDOC_Codigo = '" & dcb.TDOC_Codigo & "' AND DCI_Codigo = '" & dcb.DCI_Codigo & "' " &
                              "AND BAL_Codigo = '" & dcb.BAL_Codigo & "' AND DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If CInt(ds.Tables(0).Rows(0)("CONTAR").ToString.Trim) <> 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function verificarBandejaAsignadoBalanza(CodigoBandeja As String, codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT DISTINCT(dadpub.DADPUB_Balanza) AS Balanza " &
                              "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                              "INNER JOIN Bandeja b " &
                              "ON b.BAN_Codigo = dadpub.BAN_Codigo AND b.BAN_Activo = '1' AND b.BAE_Estado = '02' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.DADPU_Codigo = dadpub.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.DADP_Codigo = dadpu.DADP_Codigo AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "AND dcad.DCI_Codigo = '" & codPedido & "' " &
                              "WHERE dadpub.CIA+dadpub.SUC_Codigo+dadpub.ALM_Codigo+dadpub.BAN_Codigo = '" & CodigoBandeja & "' " &
                              "AND DADPUB_Activo = '1' AND DADPUB_FechaFin IS NOT NULL"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerBalanzaAsignadoPedido(dcb As DocumentoClienteBalanza) As String
        Dim balanza As String = ""
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT BAL_Codigo " &
                              "FROM Documento_Cliente_Balanza " &
                              "WHERE CIA = '" & dcb.CIA & " ' AND SUC_Codigo = '" & dcb.SUC_Codigo & "' " &
                              "AND TDOC_Codigo = '" & dcb.TDOC_Codigo & "' " &
                              "AND DCI_Codigo = '" & dcb.DCI_Codigo & "' AND DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            balanza = ds.Tables(0).Rows(0)("BAL_Codigo").ToString.Trim
        End If

        Return balanza
    End Function

    Function cantidadBandejasAsignadas(dcb As DocumentoClienteBalanza) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT DISTINCT dadpub.BAN_Codigo, dadpub.DADPUB_Balanza " &
                              "FROM Documento_Cliente_Balanza dcb " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.CIA = dcb.CIA AND dc.SUC_Codigo = dcb.SUC_Codigo " &
                              "AND dc.TDOC_Codigo = dcb.TDOC_Codigo AND dc.DCI_Codigo = dcb.DCI_Codigo " &
                              "AND dc.DCI_Estado = '12' " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dc.CIA = dcd.CIA AND dcd.SUC = dcd.SUC " &
                              "AND dc.TDOC_Codigo = dcd.TDOC_Codigo " &
                              "AND dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC " &
                              "AND dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
                              "AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC " &
                              "AND dadp.TDOC_Codigo = dad.TDOC_Codigo AND dadp.DCA_Codigo = dad.DCA_Codigo " &
                              "AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo " &
                              "AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo AND dadpu.DCA_Codigo = dadp.DCA_Codigo " &
                              "AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                              "ON dadpub.CIA = dadpu.CIA AND dadpub.SUC_Codigo = dadpu.SUC_Codigo " &
                              "AND dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' " &
                              "AND dadpub.DADPUB_Balanza IS NOT NULL " &
                              "WHERE dcb.CIA = '" & dcb.CIA & "' AND dcb.SUC_Codigo = '" & dcb.SUC_Codigo & "' " &
                              "AND dcb.TDOC_Codigo = '" & dcb.TDOC_Codigo & "' AND dcb.DCI_Codigo = '" & dcb.DCI_Codigo & "' " &
                              "AND dcb.DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds.Tables(0).Rows.Count
    End Function

    Function cantidadBandejasNoAsignadasDADPUB(dcb As DocumentoClienteBalanza) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT COUNT(*) AS Contar " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                              "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                              "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' AND dadpub.DADPUB_Balanza IS NULL " &
                              "WHERE dcad.DCI_Codigo = @DCI AND dcad.DCAD_Activo = '1' AND dcad.CIA_Cliente = @CIA " &
                              "AND dcad.SUC_Cliente = @SUC AND dcad.TDOC_Cliente = @TDOC AND dcad.DCAD_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@CIA", dcb.CIA)
            .Parameters.AddWithValue("@SUC", dcb.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", dcb.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", dcb.DCI_Codigo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim)
    End Function

    Function cantidadBandejasNoAsignadasDADPUBC(dcb As DocumentoClienteBalanza) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT DISTINCT dadpubc.BAN_Codigo, dadpubc.DADPUBC_Balanza " &
                              "FROM Documento_Cliente_Balanza dcb " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.CIA = dcb.CIA AND dc.SUC_Codigo = dcb.SUC_Codigo " &
                              "AND dc.TDOC_Codigo = dcb.TDOC_Codigo AND dc.DCI_Codigo = dcb.DCI_Codigo " &
                              "AND dc.DCI_Estado = '12' " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dc.CIA = dcd.CIA AND dcd.SUC = dcd.SUC " &
                              "AND dc.TDOC_Codigo = dcd.TDOC_Codigo " &
                              "AND dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC " &
                              "AND dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
                              "AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC " &
                              "AND dadp.TDOC_Codigo = dad.TDOC_Codigo AND dadp.DCA_Codigo = dad.DCA_Codigo " &
                              "AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo " &
                              "AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo AND dadpu.DCA_Codigo = dadp.DCA_Codigo " &
                              "AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Combo dadpuc " &
                              "ON dadpuc.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpuc.DADPUC_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja_Combo dadpubc " &
                              "ON dadpubc.DADPUC_Codigo = dadpuc.DADPUC_Codigo AND dadpubc.DADPUBC_Activo = '1' " &
                              "AND dadpubc.DADPUBC_Balanza IS NULL " &
                              "WHERE dcb.CIA = '" & dcb.CIA & "' AND dcb.SUC_Codigo = '" & dcb.SUC_Codigo & "' " &
                              "AND dcb.TDOC_Codigo = '" & dcb.TDOC_Codigo & "' AND dcb.DCI_Codigo = '" & dcb.DCI_Codigo & "' " &
                              "AND dcb.DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds.Tables(0).Rows.Count
    End Function

    Function obtenerTiemposBalanza(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT dcb.DCB_FechaInicio, tabla.DCB_FechaFin, DATEDIFF(minute,dcb.DCB_FechaInicio,tabla.DCB_FechaFin) AS Minutos " &
                              "FROM Documento_Cliente_Balanza dcb " &
                              "INNER JOIN (SELECT TOP 1 dcb.DCI_Codigo, dcb.DCB_FechaFin " &
                              "FROM Documento_Cliente_Bulto dcb " &
                              "WHERE dcb.DCI_Codigo = @PED AND dcb.DCB_Activo = '1') AS tabla " &
                              "ON tabla.DCI_Codigo = dcb.DCI_Codigo " &
                              "WHERE dcb.DCI_Codigo = @PED " &
                              "AND dcb.DCB_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerTiemposEmbalaje(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT TOP 1 dcb.DCB_FechaFin AS InicioEmbalaje, dc.DCI_InicioCelda as FinEmbalaje, " &
                              "DATEDIFF(minute,dcb.DCB_FechaFin,dc.DCI_InicioCelda) AS Minutos, r.RUT_Descripcion " &
                              "FROM Documento_Cliente_Bulto dcb " &
                              "LEFT JOIN Documento_Cliente dc " &
                              "ON dc.CIA = dcb.CIA AND dc.SUC_Codigo = dcb.SUC_Codigo " &
                              "AND dc.TDOC_Codigo = dcb.TDOC_Codigo AND dc.DCI_Codigo = dcb.DCI_Codigo " &
                              "LEFT JOIN Ruta r " &
                              "ON r.RUT_Codigo = dc.RUT_Codigo " &
                              "WHERE dcb.DCI_Codigo = @PED AND dcb.DCB_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class
